using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;

namespace FreeFlow.Administration
{
	/// <summary>
	/// A map in a procedure.
	/// </summary>
	public class Map : DatabaseAware, IRefreshable, IDeletable, IUpdatable
	{
		internal Map(Server server, IDataReader reader) : base(server)
		{
      LoadFromReader(reader);
		}

    /// <summary>
    /// Initializes a new instance of the <see cref="Map"/> class.
    /// </summary>
    /// <param name="server">The server.</param>
    /// <param name="mapName">Name of the map.</param>
    public Map(Server server, string mapName) : base(server)
    {
      LoadForMap(mapName);
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="Map"/> class for the specified folder.
    /// </summary>
    /// <param name="folderId">The folder ID.</param>
    /// <param name="server">The server.</param>
    public Map(string folderId, Server server) : base(server)
    {
      using (IDataReader reader = Server.ExecuteReader(
               string.Format("SELECT eMapName FROM eFolder WHERE eFolderID = '{0}'", folderId)))
      {
        if (reader.Read()) 
          name = reader["eMapName"].ToString();
      }
      LoadForMap(name);
    }

    private void LoadForMap(string mapName)
    {
      using (IDataReader reader = Server.ExecuteReader(
        string.Format("SELECT * FROM eMap WHERE eMapName = '{0}'", mapName)))
      {
        if (reader.Read()) 
          LoadFromReader(reader);
      }
    }

    private void LoadFromReader(IDataReader reader)
    {
      name = reader["eMapName"].ToString();
      oldName = name;
      tableName = reader["eTableName"].ToString();
      short isAdminTest = 0;
      if (reader["eAdmin"] != System.DBNull.Value)
        isAdminTest = Convert.ToInt16(reader["eAdmin"]);
      isAdministration = (isAdminTest == 1);
      procedureName = reader["eProcedureName"].ToString();
      subject = reader["eSubject"].ToString();
      prefix = reader["ePrefix"].ToString();
      if (reader["eSuffixSize"] != System.DBNull.Value)
        suffixSize = Convert.ToInt16(reader["eSuffixSize"]);
      limitAccessTo = reader["eSearchList"].ToString();
      if (Server.SchemaVersion >= SchemaVersion.Version75)
      {
        if (reader["eDelegateEvents"] != System.DBNull.Value)
          delegateEvents = Convert.ToBoolean(reader["eDelegateEvents"]);
        if (reader["eDelegateEventsBeforeLocal"] != System.DBNull.Value)
          delegateEventsBeforeLocal = Convert.ToBoolean(reader["eDelegateEventsBeforeLocal"]);
      }
    }

    private string name;
    private string oldName;
    /// <summary>
    /// Gets or sets the name of the map.
    /// </summary>
    public string Name
    {
      get
      {
        return name;
      }
      set
      {
        name = value;
      }
    }

    private string tableName;
    /// <summary>
    /// Gets the name of the table used to store custom variables.
    /// </summary>
    /// <value>The name of the table.</value>
    public string TableName
    {
      get
      {
        return tableName;
      }
    }

    private string procedureName;
    /// <summary>
    /// Gets the name of the procedure that this map is part of.
    /// </summary>
    public string ProcedureName
    {
      get
      {
        return procedureName;
      }
    }

    private string subject;
    /// <summary>
    /// Gets the code evaluated to get the subject of the map.
    /// </summary>
    public string Subject
    {
      get
      {
        return subject;
      }
    }

    private string prefix;
    /// <summary>
    /// Gets the prefix of folder names in this map.
    /// </summary>
    public string Prefix
    {
      get
      {
        return prefix;
      }
    }

    private int suffixSize;
    /// <summary>
    /// Gets the size of the suffix for folder names in this map.
    /// </summary>
    public int SuffixSize
    {
      get
      {
        return suffixSize;
      }
    }

    private int currentFolderSuffix = -1;
    /// <summary>
    /// Gets the folder suffix that was used for the last folder created.
    /// </summary>
    public int CurrentFolderSuffix
    {
      get
      {
        if (Server.DatabaseType != DatabaseType.SqlServer)
          throw new FreeFlowException("This property is not currently supported on Oracle");

        if (currentFolderSuffix == -1)
        {
          // get current folder suffix 
          object currentFolderSuffixObject = Server.ExecuteScalar(
            string.Format("SELECT MAX(eFolderNameSuffix) FROM seq{0}", tableName));
          if (currentFolderSuffixObject == System.DBNull.Value)
            currentFolderSuffix = 0;
          else
            currentFolderSuffix = Convert.ToInt32(currentFolderSuffixObject);
        }
        return currentFolderSuffix;
      }
    }

    /// <summary>
    /// Resets the folder suffix so new folder names will start again from 1.
    /// </summary>
    public void ResetFolderSuffix()
    {
      if (Server.DatabaseType != DatabaseType.SqlServer)
        throw new FreeFlowException("This method is not currently supported on Oracle");

      using (IDbTransaction transaction = Server.BeginTransaction())
      {
        Server.ExecuteNonQuery(transaction, string.Format("DELETE FROM seq{0}", tableName));
        Server.ExecuteNonQuery(transaction, string.Format("DBCC CHECKIDENT ('seq{0}', RESEED, 0)", tableName));
        currentFolderSuffix = -1;

        transaction.Commit();
      }
    }

    private string limitAccessTo;
    /// <summary>
    /// Gets the roles that are able to view folders in this map.
    /// </summary>
    public string LimitAccessTo
    {
      get
      {
        return limitAccessTo;
      }
    }

    private bool delegateEvents;
    /// <summary>
    /// Gets a value indicating whether events will be delegated to an external .NET assembly.
    /// </summary>
    /// <value><c>true</c> if events will be delegated to an external .NET assembly; otherwise, <c>false</c>.</value>
    public bool DelegateEvents
    {
      get
      {
        return delegateEvents;
      }
    }

    private bool delegateEventsBeforeLocal;
    /// <summary>
    /// Gets a value indicating whether delegated events will be executed before the local code.
    /// </summary>
    /// <value>
    /// 	<c>true</c> if delegated events will be executed before the local code; otherwise, <c>false</c>.
    /// </value>
    public bool DelegateEventsBeforeLocal
    {
      get
      {
        return delegateEventsBeforeLocal;
      }
    }

    private ActionCollection creationActions;
    /// <summary>
    /// Gets the creation actions for this map.
    /// </summary>
    public ActionCollection CreationActions
    {
      get
      {
        if (creationActions == null)
          creationActions = new ActionCollection(Server, name);
        return creationActions;
      }
    }

    private StageCollection stages;
    /// <summary>
    /// Gets the stages within this map.
    /// </summary>
    public StageCollection Stages
    {
      get
      {
        if (stages == null)
          stages = new StageCollection(Server, this);
        return stages;
      }
    }

    /// <summary>
    /// Gets the specified action that leaves from the specified stage name.
    /// </summary>
    /// <param name="stageName">Name of the stage.</param>
    /// <param name="actionName">Name of the action.</param>
    /// <returns>The action, or null if the stae or action does not exist</returns>
    public Action Action(string stageName, string actionName)
    {
      Stage stage = Stages[stageName];
      if (stage != null)
        return stage.Actions[actionName];
      return null;
    }

    private ColumnCollection variables;
    /// <summary>
    /// Gets the custom variables within this map.
    /// </summary>
    /// <value>The variables.</value>
    public ColumnCollection Variables
    {
      get
      {
        if (variables == null)
          variables = new ColumnCollection(Server, tableName, false); 
        return variables;
      }
    }

    private bool isAdministration;
    /// <summary>
    /// Gets a value indicating whether this map is a collection of administration forms.
    /// </summary>
    /// <value>
    /// 	<c>true</c> if this map is a collection of administration forms; otherwise, <c>false</c>.
    /// </value>
    public bool IsAdministration
    {
      get
      {
        return isAdministration;
      }
    }

    private DesignerLog designerLog;
    /// <summary>
    /// Gets the designer log entries for this map.
    /// </summary>
    public DesignerLog DesignerLog
    {
      get
      {
        if (designerLog == null)
          designerLog = new DesignerLog(Server, this);
        return designerLog;
      }
    }

    private FolderCollection folders;
    /// <summary>
    /// Gets the folders created for this map.
    /// </summary>
    public FolderCollection Folders
    {
      get
      {
        if (folders == null)
          folders = new FolderCollection(Server, this);
        return folders;
      }
    }

    private FormCollection forms;
    /// <summary>
    /// Gets the forms for this map.
    /// </summary>
    public FormCollection Forms
    {
      get
      {
        if (forms == null)
          forms = new FormCollection(Server, this);
        return forms;
      }
    }

    #region IRefreshable Members

    /// <summary>
    /// Refreshes the map.
    /// </summary>
    public void Refresh()
    {
      folders = null;
      designerLog = null;
      variables = null;
      stages = null;
      forms = null;
      attachments = null;
      scripts = null;
      currentFolderSuffix = -1;
      activeFolderCount = -1;
      eventCount = -1;
      averageTime = TimeSpan.MinValue;
      LoadForMap(name);
    }

    #endregion
    #region IDeletable Members

    /// <summary>
    /// Deletes this map from the database.
    /// </summary>
    public void Delete()
    {
      using (IDbTransaction transaction = Server.BeginTransaction())
      {
        Delete(transaction);

        transaction.Commit();
      }
    }

    internal void Delete(IDbTransaction transaction)
    {      
      // delete from eAttachment
      List<string> folderAttachments = new List<string>();
      using (IDataReader reader = Server.ExecuteReader(transaction,
               string.Format("SELECT eFolderID FROM eFolder WHERE eMapName = '{0}'", name)))
      {
        while (reader.Read())
        {
          folderAttachments.Add(reader["eFolderID"].ToString());
        }
      }

      for (int i = 0; i < folderAttachments.Count; i++)
      {
        Server.ExecuteNonQuery(transaction,
          string.Format("DELETE FROM eAttachment WHERE eKey LIKE '0\t{0}\t%'", folderAttachments[i]));
      }

      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eWait WHERE (eMapName = '{0}') OR (eFolderID IN (SELECT eFolderID FROM eFolder WHERE eMapName = '{0}'))", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eEvent WHERE eMapName = '{0}'", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eFolder WHERE eMapName = '{0}'", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eAction WHERE eMapName = '{0}'", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eStage WHERE eMapName = '{0}'", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eStart WHERE eMapName = '{0}'", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("UPDATE eAlert SET eAlertTime = " + Server.GetDate + ", eAlertMessage = 'Deleted by Administrator', eAlertType = '~' WHERE eMapName = '{0}'", name));
      if (Server.DatabaseType == DatabaseType.Oracle)
      {
        object count = Server.ExecuteScalar(transaction, string.Format("select count(*) from all_tables WHERE table_name='{0}'", tableName.ToUpper()));
        if (Convert.ToInt32(count) > 0)
          Server.ExecuteNonQuery(transaction, string.Format("DROP TABLE {0}", tableName));
      }
      else
        Server.ExecuteNonQuery(transaction,
          string.Format("if exists (select 1 from sysobjects where id = object_id('{0}') and sysstat & 0xf = 3) DROP TABLE {0}", tableName));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eAttachment WHERE eKey LIKE '2\t{0}\t%'", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE from eField WHERE eTableName IN " + 
        "(SELECT eTableName FROM eMap WHERE eMapName = '{0}')", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE from eForm WHERE eTableName IN " + 
        "(SELECT eTableName FROM eMap WHERE eMapName = '{0}')", name));
      Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eMap WHERE eMapName = '{0}'", name));
      Server.ExecuteNonQuery(transaction, 
        string.Format("DELETE FROM eLog WHERE eMapName = '{0}'", name));
      if (Server.SchemaVersion >= SchemaVersion.Version70)
      {
        Server.ExecuteNonQuery(transaction,
        string.Format("DELETE FROM eMapRole WHERE eMapName = '{0}'", name));
      }
    }

    #endregion
    #region IUpdatable Members

    /// <summary>
    /// Applies the changes made to this map.
    /// </summary>
    public void ApplyChanges()
    {
      if (name != oldName)
      {
        List<string> attachments = new List<string>();
        using (IDataReader reader = Server.ExecuteReader(
                 string.Format("SELECT eKey FROM eAttachment WHERE eKey LIKE '{0}\t{1}\t%'", (int)AttachmentType.Map, oldName)))
        {
          while (reader.Read())
          {
            attachments.Add(reader["eKey"].ToString());  
          }
        }

        int varCount = Variables.Count;
        using (IDbTransaction transaction = Server.BeginTransaction())
        {
          // update attachments table
          for (int i = 0; i < attachments.Count; i++)
          {
            string key = (string)attachments[i];
            AttachmentType type;
            string owner;
            string fileName;
            Attachment.ParseKey(key, out type, out owner, out fileName);
            Server.ExecuteNonQuery(transaction, 
              string.Format("UPDATE eAttachment SET eKey = '{0}\t{1}\t{2}' WHERE eKey = '{3}'",
              (int)AttachmentType.Map, name, fileName, key));
          }

          string newTableName = name.Replace(" ", "_");

          // constraints on custom variables
          if (Server.DatabaseType == DatabaseType.SqlServer)
          {
            for (int i = 0; i < varCount; i++)
            {
              if (variables[i].ConstraintExists(transaction))
                Server.ExecuteNonQuery(transaction,
                  string.Format("EXEC sp_rename '{0}', 'eConstraint{1}{2}'",
                    variables[i].ConstraintName, newTableName, variables[i].Name));
            }
          }

          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eWait SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eEvent SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eFolder SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eAction SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eStage SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eStart SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eAlert SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          if (Server.DatabaseType == DatabaseType.SqlServer)
          {
            Server.ExecuteNonQuery(transaction,
              string.Format("EXEC sp_rename '{0}', '{1}'", tableName, newTableName));
            Server.ExecuteNonQuery(transaction,
              string.Format("EXEC sp_rename 'seq{0}', 'seq{1}'", tableName, newTableName));
          }
          else
          {
            Server.ExecuteNonQuery(transaction,
              string.Format("ALTER TABLE {0} RENAME TO {1}", tableName, newTableName));
          }
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eField SET eTableName = '{0}' WHERE eTableName = '{1}'", newTableName, tableName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eForm SET eTableName = '{0}' WHERE eTableName = '{1}'", newTableName, tableName));
          Server.ExecuteNonQuery(transaction,
            string.Format("UPDATE eMap SET eMapName = '{0}', eTableName = '{1}' WHERE eMapName = '{2}'", name, newTableName, oldName));
          Server.ExecuteNonQuery(transaction, 
            string.Format("UPDATE eLog SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          if (Server.SchemaVersion >= SchemaVersion.Version70)
          {
            Server.ExecuteNonQuery(transaction,
              string.Format("UPDATE eMapRole SET eMapName = '{0}' WHERE eMapName = '{1}'", name, oldName));
          }
          
          oldName = name;
          transaction.Commit();
        }
      }
    }

    #endregion

    private AttachmentCollection attachments;
    /// <summary>
    /// Gets the attachments used within this map.
    /// </summary>
    public AttachmentCollection Attachments
    {
      get
      {
        if (attachments == null)
          attachments = new AttachmentCollection(Server, AttachmentType.Map, name);
        return attachments;
      }
    }

    private ScriptCollection scripts;
    /// <summary>
    /// Gets the scripts for this map.
    /// </summary>
    public ScriptCollection Scripts
    {
      get
      {
        if (scripts == null)
          scripts = new ScriptCollection(Server, this);
        return scripts;
      }
    }

    /// <summary>
    /// Removes any stages from the map that are no longer defined in the latest published version of the procedure and have no folders still at that stage.
    /// </summary>
    /// <returns>The number of stages that were purged</returns>
    public int PurgeStages()
    {
      using (IDbTransaction transaction = Server.BeginTransaction())
      {
        int stagesPurged = Server.ExecuteNonQuery(transaction,
          string.Format("DELETE FROM eStage WHERE eStageName IN (select eStage.eStageName FROM " +
            "eMap INNER JOIN eStage ON eStage.eMapName = eMap.eMapName AND " +
            "eStage.eLoadedTime <> eMap.eLoadedTime AND eMap.eMapName='{0}' " +
            "LEFT JOIN eFolder ON eFolder.eMapName = eMap.eMapName AND eMap.eMapName='{0}' " +
            "AND eFolder.eStageName = eStage.eStageName group by " +
            "eMap.eProcedureName, eMap.eMapName, eStage.eStageName HAVING COUNT(eFolderID) = 0)",
            SqlEscape(name)));
        Server.ExecuteNonQuery(transaction,
          string.Format("DELETE FROM eAction WHERE eMapName='{0}' AND eStageName NOT IN " +
            "(SELECT eStageName FROM eStage WHERE eMapName='{0}') AND eStageName <> ' '",
            SqlEscape(name)));

        transaction.Commit();

        return stagesPurged;
      }
    }

    private int activeFolderCount = -1;
    /// <summary>
    /// Gets the number of folders for this map that have not been archived.
    /// </summary>
    public int ActiveFolderCount
    {
      get
      {
        if (activeFolderCount == -1)
        {
          activeFolderCount = Convert.ToInt32(Server.ExecuteScalar(string.Format(
            "SELECT COUNT(*) FROM eFolder WHERE eArchived=0 AND eMapName='{0}'", SqlEscape(name))));
        }
        return activeFolderCount;
      }
    }

    private int eventCount = -1;
    /// <summary>
    /// Gets the total number of events that have occurred on folders of theis type 
    /// </summary>
    public int EventCount
    {
      get
      {
        if (eventCount == -1)
        {
          eventCount = Convert.ToInt32(Server.ExecuteScalar(string.Format(
            "SELECT COUNT(*) FROM eEvent {0} WHERE eMapName='{1}'", Server.WithNoLock, SqlEscape(name))));
        }
        return eventCount;
      }
    }

    internal TimeSpan averageTime = TimeSpan.MinValue;
    /// <summary>
    /// Gets the average time folders take to complete their progress through the map.
    /// </summary>
    public TimeSpan AverageTime
    {
      get
      {
        if (averageTime == TimeSpan.MinValue)
        {
          int count = Convert.ToInt32(Server.ExecuteScalar(string.Format(
            "SELECT SUM(DATEDIFF(MINUTE,eFolder.eCreationTime,eFolder.eEntryTime))/COUNT(*) FROM eFolder " +
            "WHERE eMapName='{0}' AND eFolder.eArchived=1", SqlEscape(name))));
          averageTime = TimeSpan.FromMinutes(count);
        }
        return averageTime;
      }
    }

    internal void GetStatistics()
    {
      // set all stats to zero
      foreach (Action action in CreationActions)
      {
        action.eventCount = 0;
        action.foldersPassThroughCount = 0;
      }

      foreach (Stage stage in Stages)
      {
        stage.eventCount = 0;
        stage.foldersPassThroughCount = 0;
        stage.averageTime = TimeSpan.Zero;
        foreach (Action action in stage.Actions)
        {
          action.eventCount = 0;
          action.foldersPassThroughCount = 0;
        }
      }

      // stage statistics
      using (IDataReader reader = Server.ExecuteReader(
        string.Format("SELECT eToStage, COUNT(*) AS Total, COUNT(DISTINCT eFolderID) AS Folders " +
        "FROM eEvent {0} WHERE eMapName='{1}' GROUP BY eToStage", Server.WithNoLock, SqlEscape(name))))
      {
        while (reader.Read())
        {
          Stage thisStage = Stages[reader["eToStage"].ToString()];
          if (thisStage != null)
          {
            thisStage.eventCount = Convert.ToInt32(reader["Total"]);
            thisStage.foldersPassThroughCount = Convert.ToInt32(reader["Folders"]);
          }
        }
      }

      // average times
      using (IDataReader reader = Server.ExecuteReader(string.Format("SELECT eFromStage, " +
          "SUM(DATEDIFF(MINUTE,ISNULL(eEntryTime, eEventTime), eEventTime))/(COUNT(DISTINCT(eFolderID))) As AvgTime " +
          "FROM eEvent {0} WHERE eMapName='{1}' GROUP BY eFromStage", Server.WithNoLock, SqlEscape(name))))
      {
        while (reader.Read())
        {
          Stage thisStage = Stages[reader["eFromStage"].ToString()];
          if (thisStage != null)
          {
            thisStage.averageTime = TimeSpan.FromMinutes(Convert.ToInt32(reader["AvgTime"]));
          }
        }
      }

      // action statistics
      using (IDataReader reader = Server.ExecuteReader(
        string.Format("SELECT eFromStage, eActionName, COUNT(*) AS Total, COUNT(DISTINCT eFolderID) AS Folders FROM eEvent {0} WHERE eMapName='{1}' " +
          "GROUP BY eFromStage, eActionName", Server.WithNoLock, SqlEscape(name))))
      {
        while (reader.Read())
        {
          string stageName = reader["eFromStage"].ToString();
          string actionName = reader["eActionName"].ToString();
          Action thisAction;
          if (string.IsNullOrEmpty(stageName))
            thisAction = CreationActions[actionName];
          else
            thisAction = Action(stageName, actionName);
          if (thisAction != null)
          {
            thisAction.eventCount = Convert.ToInt32(reader["Total"]);
            thisAction.foldersPassThroughCount = Convert.ToInt32(reader["Folders"]);
          }
        }
      }    
    }
  }
}
